﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SQLServerHealthCheck.Model;

namespace SQLServerHealthCheck.SQLServer.IssueTypes
{
    public class DatabaseCheckTempdbAndDataSameDrive:IssueTypeCheckBase
    {

        public DatabaseCheckTempdbAndDataSameDrive()
        {
            IssueTypeId = 3008;
        }

        public override void Check()
        {
            HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]);
            Issue issue;

            // Get all databases with name tempdb
            // 

            var qrytempdb = from d in dc.Databases
                          from df in dc.DatabaseFiles

                          where df.File.Contains("tempdb.mdf")                        

                         
                          select d;

           foreach(var tempdb in qrytempdb)        
           { // declare sting for tempdb drive             
                string tempDrive = "";
                
             // declare count to hold count of database files that are  on same drive as TEMPDB  
               
               int countDatafiles = 0;

               DatabaseFile tempdatafile =tempdb.DatabaseFiles.FirstOrDefault(i => i.File.Contains("mdf"));

               if(tempdatafile!=null)
               {
                   tempDrive = tempdatafile.Drive;

                   var qryDrive = from d in tempdb.Service.Databases
                                   from df in dc.DatabaseFiles

                                   where df.Drive == tempDrive


                                   select d;

                   countDatafiles = qryDrive.Count();


              if(countDatafiles > 0)
              {
               
               int databaseId = tempdb.DatabaseID;
               //Get Issuetype object

               issue = new Issue();

               //Get Issuetype object  And Create a new  Global issue object 
               IssueType issueType = dc.IssueTypes.FirstOrDefault(i => i.IssueTypeId == IssueTypeId);
               
               issue.IssueType = issueType;
               issue.TargetId = tempdb.DatabaseID;
               issue.Description = issueType.Description.Replace("{Database}", tempdb.DatabaseName).Replace("{InstanceName}",tempdb.Service.InstanceName);
              
               dc.Issues.InsertOnSubmit(issue);  
              }
               
              }
           }
          
           dc.SubmitChanges();    
           
           
           }
        
        
        
         
         
         
   }
}
